/*
* web数据库
* */
define(function(){
    var utils = utils || {};

    ( function() {

        var self = utils;

        self.toArray = function(list) {
            return Array.prototype.slice.call(list);
        };

        self.isUndef = function(obj) {
            return obj === undefined;
        };

        self.isObject = function(A) {
            return (typeof A === "object") && (A !== null);
        };

        self.isFunction = function(object) {
            return !!(object && object.constructor && object.call && object.apply);
        };

        var types = ['Arguments', 'Function', 'String', 'Number', 'Date', 'RegExp'];
        types.forEach( function(name) {
            self['is' + name] = function(obj) {
                return Object.prototype.toString.call(obj) === '[object ' + name + ']';
            };
        });

        self.isBoolean = function(obj) {
            return obj === true || obj === false || Object.prototype.toString.call(obj) === '[object Boolean]';
        };

        self.each = function(obj, cb) {
            for(var k in obj) {
                cb(obj[k], k);
            }
        };

        utils.has = function(obj, key) {
            return hasOwnProperty.call(obj, key);
        };

        self.format = function(str) {
            var args = arguments;
            return str.replace(/{(\d+)}/g, function(match, number) {
                var num = parseInt(number,10);
                return isFinite(num) ? args[1+num] : match;
            });
        };



    } () );
    var websql = websql || {};
    var utils = utils || {};

    websql.Db = function(db, version, desc, size) {
        var self = this;
        this.dbType = "SQLite";
        this.tables = [];
        var regexIso8601 = /^(\d{4}|\+\d{6})(?:-(\d{2})(?:-(\d{2})(?:T(\d{2}):(\d{2}):(\d{2})\.(\d{1,3})(?:Z|([\-+])(\d{2}):(\d{2}))?)?)?)?$/;
        this.cs = {
            //sql types
            pk: 'pk', text: 'text', integer: 'integer', numeric: 'numeric', date: 'date', bool: 'boolean',
            //query types
            rowset: 'rowset', row: 'row', scalar: 'scalar', nonQuery: 'non-query', insert: 'insert', any: 'any'
        };


        this.client = openDatabase(db, version || "1.0", desc || db, size || (2 * 1024 * 1024));

        this.tableSQL =	"SELECT name FROM sqlite_master	WHERE type='table' ORDER BY name";

        this.modelsTableSchema = {
            model: 'text'
        };

        this.model = {
            tables: {}
        };

        self.debug = false;
        self.log = function(obj) {
            if(self.debug) {
                console.log(obj);
            }
        };

        this.placeholder = function() {
            return "?";
        };

        this.typeToDb = function (p) {
            if (utils.isDate(p)) {
                return p.toISOString();
            }
            if (utils.isBoolean(p)) {
                return p ? 1 : 0;
            }
            return p;
        };

        self.inferQueryType = function(sql) {
            var mysql = sql.toLowerCase();
            if(mysql.indexOf('insert') === 0) {
                return self.cs.insert;
            }

            if(mysql.indexOf('select') === 0) {
                if(mysql.indexOf('limit(1)') > 0) {
                    return self.cs.row;
                }

                return self.cs.rowset;
            }

            if(mysql.indexOf('update') === 0 || mysql.indexOf('delete') === 0) {
                return self.cs.nonQuery;
            }

            return self.cs.any;
        };

        this.processRow = function (row) {
            var obj = {};
            for (var key in row) {
                var value = row[key];
                if (utils.isString(value) && value.match(regexIso8601)) {
                    var d = Date.parse(value);
                    if (d) {
                        value = new Date(d);
                    }
                }
                obj[key] = value;
            }
            return obj;
        };

        self.processResultType = function(results, type) {
            switch (type) {
                case self.cs.any:
                    return results;
                case self.cs.insert:
                    return results.insertId;
                case self.cs.rowset:
                    var len = results.rows.length, i;
                    var rows = [];
                    for (i = 0; i < len; i++) {
                        var item = self.processRow(results.rows.item(i));
                        rows.push(item);
                    }
                    return rows;
                case self.cs.row:
                    if(results.rows.length) {
                        return self.processRow(results.rows.item(0));
                    }
                    return null;
                case self.cs.scalar:
                    if(results.rows.length) {
                        var obj = self.processRow(results.rows.item(0));
                        for(var key in obj) {
                            return obj[key];
                        }
                    }
                    return null;
                case self.cs.nonQuery:
                    return results.rowsAffected;
                default:
                    return results;
            }

        };

        var _translateType = function (typeName) {
            var _result = typeName;

            switch (typeName) {
                case "pk":
                    _result = "INTEGER PRIMARY KEY  AUTOINCREMENT";
                    break;
                case "int":
                    _result = "INTEGER";
                    break;
                case "decimal":
                    _result = "numeric";
                    break;
                case "date":
                    _result = "datetime";
                    break;
                case "text":
                    _result = "text";
                    break;
                case "boolean":
                    _result = "boolean";
                    break;
            }
            return _result;
        };

        this.forward = function(res, tx) {
            return $.Deferred( function(d) {
                d.resolve(res, tx);
            }).promise();
        };

        var successWrapper = function (d) {
            return function (tx, results) {
                var ret = self.processResultType(results, d.queryType);
                d.resolve(ret, tx);
            };
        };

        var failureWrapper = function (d) {
            return function (tx, error) {
                self.log("sql error on : " + d.sql + " --- message: " + error.message);
                d.reject(error);
                return true;
            };
        };

        this._exec = function(sql, params, tx, queryType) {
            var sp = params ? params.join(", ") : "" ;
            self.log("exec : " + sql + " : [" + sp  + "]");
            return $.Deferred(function (d) {
                var _args = params ? params.map(self.typeToDb) : params;
                d.sql = sql;
                d.queryType = queryType || self.cs.any;
                if(tx) {
                    tx.executeSql(sql, _args, successWrapper(d), failureWrapper(d));
                } else {
                    self.client.transaction(function(tx1){
                        tx1.executeSql(sql, _args, successWrapper(d), failureWrapper(d));
                    });
                }
            });
        };

        self.query = function(sql, params, tx) {
            return self._exec(sql, params, tx, self.cs.rowset);
        };

        self.queryOne = function(sql, params, tx) {
            return self._exec(sql, params, tx, self.cs.row);
        };

        self.scalar = function(sql, params, tx) {
            return self._exec(sql, params, tx, self.cs.scalar);
        };

        self.execNonQuery = function(sql, params, tx) {
            return self._exec(sql, params, tx, self.cs.nonQuery);
        };

        self.exec = function (sql, params, tx, queryType) {
            var qt = queryType || self.inferQueryType(sql);
            return self._exec(sql, params, tx, qt);
        };

        this.getQuery = function (sql, params, queryType) {
            return new websql.Query(sql, params, new websql.Table('', '', self), queryType);
        };

        this.fnRunQuery = function(qry) {
            return function( res, tx ) {
                return qry.run(tx);
            };
        };

        this.runQueries = function( queries, tx ) {
            var promise = queries[0].run(tx);
            for( var i = 1; i < queries.length; i++) {
                var qry = queries[i];
                promise = promise.then( self.fnRunQuery(qry) );
            }
            return promise;
        };

        this.runSqls = function( sqls, tx ) {
            var table = new websql.Table('', '', self);
            var queries = sqls.map(function(sql) {
                if(Array.isArray(sql)) {
                    return new websql.Query( sql[0], sql.slice(1), table, self.cs.any );
                } else {
                    return new websql.Query( sql.toString(), [], table, table, self.cs.any );
                }
            });
            return self.runQueries( queries, tx );
        };

        this.dropTable = function (tableName) {
            return new websql.Query("DROP TABLE IF EXISTS " + tableName, [], new websql.Table(tableName, "", self), self.cs.any);
        };

        var _createColumn = function (columnName, columnProps) {
            if(utils.isString(columnProps)) {
                return columnName + " " + _translateType(columnProps);
            }
            return columnName + " " + _translateType(columnProps.type) +
                    ( columnProps.required ? " NOT NULL" : "" ) +
                    ( columnProps.unique ? " UNIQUE" : "");
        };

        this.createTable = function (tableName, columns, checkExists) {

            var _sql = "CREATE TABLE " + ( checkExists ? "IF NOT EXISTS " : "" ) + tableName + "(";
            var _cols = [];

            _cols.push( _createColumn( 'id', "pk" ) );
            for (var c in columns) {
                if (c === "timestamps") {
                    _cols.push("created_at int");
                    _cols.push("updated_at int");
                } else if (c !== 'id') {
                    _cols.push( _createColumn( c, columns[c] ) );
                }
            }


            _sql += _cols.join(", ") + ")";
            return new websql.Query(_sql, [], new websql.Table(tableName, "id", self), self.cs.any);
        };

        this.createColumn = function(tableName, columnName, columnProps) {
            return new websql.Query("ALTER TABLE " + tableName + " ADD COLUMN " + _createColumn( columnName, columnProps ), [], new websql.Table(tableName, "", self), self.cs.any);
        };

        this.createModelsTable = function() {
            return self.createTable('_models', self.modelsTableSchema, true /* if not exists */);
        };

        this.loadModel = function(tx) {
            var p = self.createModelsTable().run(tx)
                    .then( function( res, tx ) {
                        self._models = new websql.Table("_models", "id", self);
                        return self._models.last(tx);
                    })
                    .then( function(modelAsJson, tx) {
                        if(modelAsJson) {
                            self.model = JSON.parse(modelAsJson.model);
                        }
                        return self.forward(self.newModel, tx);
                    });
            return p;
        };

        this.reloadModel = function(tx) {
            self._models.last(tx)
                    .then( function(modelAsJson, tx) {
                        if(modelAsJson) {
                            self.model = JSON.parse(modelAsJson.model);
                        }
                        return self.forward(self.newModel, tx);
                    });
        };

        this.upgrade = function(newModel, tx) {
            return self.loadModel(tx)
                    .then(function(res, tx) {
                        var queries = [];
                        //self.model now contains the current model
                        utils.each( newModel.tables, function(table, tableName) {
                            if( ! utils.has( self.model.tables, tableName ) ) {
                                queries.push( self.createTable( tableName, table ) );
                            } else {
                                var oldColumns = self.model.tables[tableName];
                                var newColumns = newModel.tables[tableName];
                                utils.each( newColumns, function (column, columnName) {
                                    if( ! utils.has(oldColumns, columnName) ) {
                                        queries.push( self.createColumn( tableName, columnName, column ) );
                                    }
                                });
                            }
                        });
                        if(queries.length) {
                            queries.push( self._models.insert( { model: JSON.stringify(newModel) }) );
                            return self.runQueries( queries, tx );
                        } else {
                            return self.forward(res, tx);
                        }

                    })
                    .then(function(res, tx) {
                        self.model = newModel;
                        utils.each( newModel.tables, function(table, tableName) {
                            self[tableName] = new websql.Table(tableName, "id", self);
                        });
                        return self.forward(res, tx);
                    });
        };


    };

    websql.Query = function(sql, params, table, queryType) {

        var operationsMap = {
            '=': '=',
            '!': '!=',
            '>': '>',
            '<': '<',
            '>=': '>=',
            '<=': '<=',
            '!=': '<>',
            '<>': '<>'
        };

        var self = this;
        self.sql = sql;
        self.params = params || [];
        self.table = table;
        self.db = table.db;
        self.queryType = queryType;


        self.append = function (sql) {
            self.sql += (arguments.length === 1 ? sql : utils.format.apply(null, utils.toArray(arguments)) );
            return self;
        };

        self.order = function (sort, desc) {
            return self.append(" ORDER BY {0}{1}", sort, (desc ? " DESC" : "") );
        };

        self.limit = function (count, offset) {
            return utils.isUndef(offset) ? self.append(" LIMIT {0}", count) : self.append(" LIMIT {0} OFFSET {1}", count, offset);
        };

        self.first = function () {
            return self.append(" LIMIT(1)");
        };

        self.last = function () {
            return self.append(" ORDER BY {0} DESC LIMIT(1)", self.table.pk);
        };

        self.where = function (conditions) {
            if (utils.isUndef(conditions)) {
                return self;
            }

            if (utils.isNumber(conditions)) {
                return self.append(' WHERE "{0}" = {1}', self.table.pk, conditions);
            }

            if (utils.isString(conditions)) {
                self.params.push(conditions);
                return self.append(' WHERE "{0}" = {1}', self.table.pk, self.db.placeholder(self.params.length));
            }

            var _conditions = [];
            for(var key in conditions) {
                var value = conditions[key];

                var parts = key.trim().split(/ +/);
                var property = parts[0];
                var operation = operationsMap[parts[1]] || '=';

                if (!Array.isArray(value)) {
                    self.params.push(value);
                    _conditions.push(utils.format('"{0}" {1} {2}', property, operation, self.db.placeholder(self.params.length)));
                } else {
                    var arrayConditions = [];
                    value.forEach(function(v) {
                        self.params.push(v);
                        arrayConditions.push(self.db.placeholder(self.params.length));
                    });
                    _conditions.push(utils.format('"{0}" {1} ({2})', property, operation === '!=' || operation === '<>' ? 'NOT IN' : 'IN', arrayConditions.join(', ')));
                }
            }
            return self.append(' WHERE ' + _conditions.join(' AND '));
        };

        self.parseArgs = function (args) {
            var _args = utils.toArray(args);

            if (_args.length === 0) {
                return self;
            }

            var columns;

            _args.forEach(function(arg) {
                if (utils.isNumber(arg) || utils.isString(arg)) {
                    var criteria = {};
                    criteria[self.table.pk] = arg;
                    self.where(criteria);
                } else  if (Array.isArray(arg)) {
                    columns = arg;
                } else  if(utils.isObject(arg)) {
                    var where = arg.where || arg;
                    columns = arg.columns;

                    if (utils.isObject(where)) {
                        self.where(where);
                    }
                }
            });

            if(columns) {
                self.sql = self.sql.replace("*", columns.join(",") );
            }

            return self;
        };

        this.find = function() {
            self.sql = "select * from " + self.table.name;
            //self.queryType = self.db.cs.rowset;
            return self.parseArgs(arguments);
        };

        this.run = function(tx, queryType) {
            var qt = queryType || self.queryType || self.db.cs.any;
            return self.db._exec(self.sql, self.params, tx, qt);
        };

        this.all = function(tx) {
            return self.db._exec(self.sql, self.params, tx, self.db.cs.rowset);
        };
        this.one = function(tx) {
            return self.db._exec(self.sql, self.params, tx, self.db.cs.row);
        };
        this.scalar = function(tx) {
            return self.db._exec(self.sql, self.params, tx, self.db.cs.scalar);
        };
        this.nonQuery = function(tx) {
            return self.db._exec(self.sql, self.params, tx, self.db.cs.nonQuery);
        };
        this.each = function(cb, tx) {
            var d = self.all(tx);
            if(cb) {
                d.done(function(res) {
                    var len = res.length;
                    for(var i=0; i<len; i++) {
                        cb(res[i]);
                    }
                });
            }
            return d;
        };

    };


    websql.Table = function (tableName, pk, _db) {
        var self = this;
        this.name = tableName;
        this.pk = pk;
        this.db = _db;

        this.find = function() {
            return new websql.Query("SELECT * FROM " + this.name, [], self, self.db.cs.rowset).parseArgs(arguments);
        };

        this.first = function(tx) {
            return self.find().first().run(tx, self.db.cs.row);
        };

        this.last = function(tx) {
            return self.find().last().run(tx, self.db.cs.row);
        };

        this.each = function(tx) {
            this.find().each(tx);
        };

        this.count = function(where, tx) {
            return new websql.Query("SELECT COUNT(1) FROM " + self.name, [] ,self, self.db.cs.scalar).where(where).run(tx);
        };

        this.all = function(tx) {
            return new websql.Query("SELECT * FROM " + self.name, [], self).all(tx);
        };

        this.insert = function(data) {
            if(!data) {
                throw "insert should be called with data";//{ return new Query().raiseError("insert should be called with data"); }
            }

            var sql = utils.format("INSERT INTO {0} ({1}) VALUES(", self.name, Object.keys(data).join(", "));
            var params = [];
            var values = [];

            var seed = 0;
            for(var key in data) {
                values.push( self.db.placeholder(++seed) );
                params.push( data[key] );
            }

            sql += values.join(", ") + ")";
            return new websql.Query(sql, params, self, self.db.cs.insert);
        };

        this.update = function(fields, where){
            if(utils.isObject(fields) === false) {
                throw "Update requires a hash of fields=>values to update to";
            }

            var params = [];
            var values = [];
            var seed = 0;

            for(var key in fields) {
                values.push( key + ' = ' + self.db.placeholder(++seed) );
                params.push( fields[key] );
            }

            var sql = utils.format("UPDATE {0} SET {1}", this.name, values.join(', '));
            return new websql.Query(sql, params, self, self.db.cs.nonQuery).where(where);
        };

        this.destroy = function() {
            return new websql.Query("DELETE FROM " + self.name, [], self, self.db.cs.nonQuery).parseArgs(arguments);
        };
    };

    return websql
});